﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using MySql.Data.MySqlClient;

namespace YunSteelyard.util
{
    public class MysqlUtil
    {
        private MySqlConnection cnn;
        private MySqlTransaction trans;

        public MysqlUtil()
        {
            cnn = new MySqlConnection("Database='dddinventory';Data Source='192.168.1.114';User Id='yanshang';Password='ys2016';charset='utf8'");
            cnn.Open();
            trans = cnn.BeginTransaction();
        }

        public MysqlUtil(Boolean isTran)
        {
            cnn = new MySqlConnection("Database='dddinventory';Data Source='192.168.1.114';User Id='yanshang';Password='ys2016';charset='utf8'");
            cnn.Open();
            if (isTran)
            {
                trans = cnn.BeginTransaction();
            }
        }
  
        public DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();
            MySqlCommand cm = new MySqlCommand();
            cm.Connection = cnn;
            try{
                MySqlDataAdapter mda = new MySqlDataAdapter(sql, cnn);
                mda.Fill(ds, "table1");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnn.Close();
            }
            return ds;
        }



        #region 执行语句更改数据库

        #region 非事物
        public int ExecuteSql(string sql)
        {
            int rt = 0;
            MySqlCommand cm = new MySqlCommand(sql);
            cm.Connection = cnn;
            try
            {
                rt = cm.ExecuteNonQuery();

                trans.Commit();

            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                cnn.Close();
            }
            // }
            return rt;
        } 
        #endregion


        #region 事物
        public int ExecuteSqlTran(string sql)
        {
            int rt = 0;
            MySqlCommand cm = new MySqlCommand(sql);
            cm.Connection = cnn;
            try
            {
                rt = cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return rt;
        }
        #endregion
        #endregion

        public void RollBack()
        {
            trans.Rollback();
        }

        public void Commit()
        {
            trans.Commit();
        }
    }
}
